#!/usr/local/bin/python

import os
import sys
import cx_Oracle
import StringIO
import socket
import time
import string

#
BB = os.environ["BB"]
BBTMP = os.environ["BBTMP"]
BBDISP = os.environ["BBDISP"]

os.environ['BBPROG'] = 'ora-tbs.py'

TEST = 'oratbs'
os.environ['TEST'] = TEST

MACHINE = socket.gethostname()
MACHINE = string.replace(MACHINE, '.', ',')

DATE = time.strftime("%a %b %d %H:%M:%S %Z %Y", time.localtime(time.time()))

#
# Edit username/password
#
connection = cx_Oracle.connect("username/password")

sql = """ SELECT F.TABLESPACE_NAME,
         TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
         TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
         TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
         TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999') PER_FREE
       FROM   (
           SELECT TABLESPACE_NAME,
           ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                  FROM V$PARAMETER
                  WHERE NAME = 'db_block_size')/1024)
              ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
         SELECT TABLESPACE_NAME,
         ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
         FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME
       ) T
       WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME ORDER BY 1
       """

cursor = connection.cursor()
cursor.arraysize = 50
cursor.execute (sql)
i1 = cursor.fetchall()

output = StringIO.StringIO() 

for z in range(i1.__len__()):
  print >> output, i1[z][0].ljust(15) + " : " + i1[z][4]

L0 = output.getvalue()
output.close()

COLOR = 'green'

LINE = "status " + MACHINE + "." + TEST + " " + COLOR + " " + DATE + " Tablespaces on " + MACHINE + '\n' + L0

cmd = BB + ' ' + BBDISP + ' "' + LINE + '"'

os.system(cmd)
